/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package model.dao;

import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Vector;
import model.pojo.adminTableColumnsPOJO;
import model.util.MySQLProviders;

/**
 *
 * @author Mr.Thanh
 */
public class administratorDAO {
    
    
    public static ArrayList<String> getAllTable(String database){
      ArrayList<String> ds = new ArrayList<String>();      
      //mo ket noi
      MySQLProviders helper = new MySQLProviders();
      helper.open();    
      
     String sql=String.format("SHOW TABLES FROM %s", database);
      ResultSet rs = helper.executeQuery(sql);
      try {
         while (rs.next()) {            
            ds.add(rs.getString(1).trim());
         }
      } catch (Exception ex) {
         System.out.println(ex.getMessage());
      }
      helper.close();
      return ds;
   }
    
    
    
    public static ArrayList<adminTableColumnsPOJO> getAllColumsFromTable(String database,String table){
      ArrayList<adminTableColumnsPOJO> ds = new ArrayList<adminTableColumnsPOJO>();      
      //mo ket noi
      MySQLProviders helper = new MySQLProviders();
      helper.open();    
      
     String sql=String.format("SHOW COLUMNS FROM %s.%s", database,table);
      ResultSet rs = helper.executeQuery(sql);
      try {
         while (rs.next()) {
            adminTableColumnsPOJO cols = new adminTableColumnsPOJO();
            cols.setField(rs.getString("Field"));
            cols.setType(rs.getString("Type"));
            cols.setNull(rs.getString("Null"));
            cols.setKey(rs.getString("Key"));
            cols.setDefault(rs.getString("Default"));
            cols.setExtra(rs.getString("Extra"));
            ds.add(cols);
         }
      } catch (Exception ex) {
         System.out.println(ex.getMessage());
      }
      helper.close();
      return ds;
   }
    
    public static HashMap<Vector,ArrayList<adminTableColumnsPOJO>> getTablePage(String database,String table,int trang){
      HashMap<Vector,ArrayList<adminTableColumnsPOJO>> ds = new HashMap<Vector,ArrayList<adminTableColumnsPOJO>>() {};      
      int soSanPhamTrenTrang = ThamSoDAO.laySoLuongTrenTrang();
      int batDau = (trang - 1) * soSanPhamTrenTrang;
      if(soSanPhamTrenTrang==batDau)
          batDau=1;   
      //mo ket noi
      MySQLProviders helper = new MySQLProviders();
      helper.open();    
      
     String sql=String.format("SELECT *FROM %s.%s LIMIT %d,%d", database,table,batDau,soSanPhamTrenTrang);
     ArrayList<adminTableColumnsPOJO> cols=getAllColumsFromTable(database, table);
      ResultSet rs = helper.executeQuery(sql);
      try {
         while (rs.next()) {
            Vector row=new Vector();
            for(adminTableColumnsPOJO c:cols){
                row.add(rs.getObject(c.getField()));
                
            }
            ds.put(row,cols );
            
         }
      } catch (Exception ex) {
         System.out.println(ex.getMessage());
      }
      helper.close();
      return ds;
   }
    
    
     public static HashMap<Vector,ArrayList<adminTableColumnsPOJO>> SearchTablePage(String table,int trang,String field,String word){
      HashMap<Vector,ArrayList<adminTableColumnsPOJO>> ds = new HashMap<Vector,ArrayList<adminTableColumnsPOJO>>() {};      
       
      int soSanPhamTrenTrang = ThamSoDAO.laySoLuongTrenTrang();
      int batDau = (trang - 1) * soSanPhamTrenTrang;
      if(soSanPhamTrenTrang==batDau)
          batDau=1;   
      //mo ket noi
      MySQLProviders helper = new MySQLProviders();
      helper.open();    
      
     String sql=String.format("SELECT *FROM `%s`.`%s` WHERE `%s`.`%s` ","vndoan",table,table,field)+" LIKE concat('%','"+word+"','%') LIMIT "+batDau+","+soSanPhamTrenTrang;
     ArrayList<adminTableColumnsPOJO> cols=getAllColumsFromTable("vndoan", table);
      ResultSet rs = helper.executeQuery(sql);
      try {
         while (rs.next()) {
            Vector row=new Vector();
            for(adminTableColumnsPOJO c:cols){
                row.add(rs.getObject(c.getField()));
                
            }
            ds.put(row,cols );
            
         }
      } catch (Exception ex) {
         System.out.println(ex.getMessage());
      }
      helper.close();
      return ds;
   }
   
    
    
    public static HashMap<Vector,ArrayList<String>> getAllTableInfo(String database){
      HashMap<Vector,ArrayList<String>> ds = new HashMap<Vector,ArrayList<String>>();      
      
      //mo ket noi
      MySQLProviders helper = new MySQLProviders();
      helper.open();    
      
     String sql=String.format("SHOW TABLE STATUS FROM %s", database);
        ArrayList<String> cols=new ArrayList();     
        cols.add("Name");
        cols.add("Engine");
        cols.add("Rows");
        cols.add("Auto_increment");
        cols.add("Create_time");
        cols.add("Collation");
        
      ResultSet rs = helper.executeQuery(sql);
      try {
         while (rs.next()) {
            Vector tbl=new Vector();
            tbl.add(rs.getString("Name"));
            tbl.add(rs.getString("Engine"));
            tbl.add(rs.getString("Rows"));
            tbl.add(rs.getString("Auto_increment"));
            tbl.add(rs.getString("Create_time"));
            tbl.add(rs.getString("Collation"));
            ds.put(tbl,cols);
            
         }
      } catch (Exception ex) {
         System.out.println(ex.getMessage());
      }
      helper.close();
      return ds;
   }
    
    
    
    public static HashMap<Vector,ArrayList<String>> getTableStruct(String database,String table){
      HashMap<Vector,ArrayList<String>> ds = new HashMap<Vector,ArrayList<String>>();      
          
        ArrayList<String> cols=new ArrayList();     
        cols.add("Field");
        cols.add("Type");
        cols.add("Null");
        cols.add("Key");
        cols.add("Default");
        cols.add("Extra");       
     
      try {
         ArrayList<adminTableColumnsPOJO> fields= getAllColumsFromTable("vndoan",table);
         for(adminTableColumnsPOJO s:fields) {
            Vector vt=new Vector();
            vt.add(s.getField());
            vt.add(s.getType());
            vt.add(s.getNull());
            vt.add(s.getKey());
            vt.add(s.getDefault());
            vt.add(s.getExtra());
            ds.put(vt,cols);
            
         }
      } catch (Exception ex) {
         System.out.println(ex.getMessage());
      }
      
      return ds;
   }
    
    
    
    
     public static Vector getRowById(String table,String fieldId,String id){
       Vector vt=new Vector();
       MySQLProviders helper = new MySQLProviders();
       helper.open();
       String sql="select *from "+table+" where "+fieldId+"='"+id+"'";
       ResultSet rs = helper.executeQuery(sql);
       try {
           while (rs.next()) {             
            int index=1;
            do{
                vt.add(rs.getObject(index)); 
                index++;
             }while(true);
            
          }
       }catch(Exception e){
           
       }finally{
           helper.close();
       }
       return vt;
   }   
    
    public static HashMap<Vector,ArrayList<String>> getTableUpdateDelete(String table,String fieldId,String id){
      HashMap<Vector,ArrayList<String>> ds = new HashMap<Vector,ArrayList<String>>();      
          
        ArrayList<String> cols=new ArrayList();     
        cols.add("Field");
        cols.add("Type");        
        cols.add("Null");
        cols.add("Key");
        cols.add("Value");               
     
      try {
          Vector vtRow=getRowById(table,fieldId,id);
         ArrayList<adminTableColumnsPOJO> fields= getAllColumsFromTable("vndoan",table);
         
         if(fields.size()==vtRow.size()){
             for(int i=0;i<fields.size();i++){
                 Vector vt=new Vector();             
                 adminTableColumnsPOJO s=fields.get(i);
                 //them ten column vao dau 1 dong cua table tren html
                 vt.add(s.getField());
                 vt.add(s.getType());                 
                 vt.add(s.getNull());
                 vt.add(s.getKey());
                 //them gia tri tuong tung voi column nay
                 vt.add(vtRow.get(i));
                ds.put(vt,cols);
             }
         }
      } catch (Exception ex) {
         System.out.println(ex.getMessage());
      }
      
      return ds;
   }
    
    
    
    public static HashMap<Vector,ArrayList<String>> getTableAdd(String table){
      HashMap<Vector,ArrayList<String>> ds = new HashMap<Vector,ArrayList<String>>();      
          
        ArrayList<String> cols=new ArrayList();     
        cols.add("Field");
        cols.add("Type");        
        cols.add("Null");
        cols.add("Key");
        cols.add("Value");            
     
      try {
          
         ArrayList<adminTableColumnsPOJO> fields= getAllColumsFromTable("vndoan",table);         
         
             for(int i=0;i<fields.size();i++){
                 Vector vt=new Vector();             
                 adminTableColumnsPOJO s=fields.get(i);
                 //them ten column vao dau 1 dong cua table tren html
                 vt.add(s.getField());
                 vt.add(s.getType());                 
                 vt.add(s.getNull());
                 vt.add(s.getKey());
                 //them gia tri tuong tung voi column nay
                 vt.add("");
                  
                ds.put(vt,cols);
             }        
      } catch (Exception ex) {
         System.out.println(ex.getMessage());
      }
      
      return ds;
   }
    
    
    public static Vector GetDataByColumnId(String columnId){
       MySQLProviders helper = new MySQLProviders();
       helper.open();
       Vector vtResult=new Vector();      
       
        ArrayList<String> dsTable=getAllTable("vndoan");
       try {
           //duyet het cac table de tim ra table ung voi khoa chinh columnId
           //sau khi tim dc ta se lay danh sach cac khoa cua table nay de hien thi len web
           String tableResult="";
           String sql="";
           for(String tbl:dsTable){
               sql="SHOW INDEX FROM vndoan."+tbl;
               ResultSet rs = helper.executeQuery(sql);
               while (rs.next()) { 
                   //kiem tra xem columnid bang voi column_name cua table nao tuc no la khoa chinh cua table do
                   //column_name la khoa chinh cua 1 table                   
                   if(columnId.trim().equals(rs.getString("Column_name")) &&rs.getString("Key_name").equals("PRIMARY")){
                       tableResult=tbl;
                       break;
                   }
               }   
               if(!tableResult.equals("")){
                   break;
               }
           }
           
           //neu tim thay table thi bat dau lay du lieu ra
           sql=String.format("select %s from %s", columnId,tableResult);
           ResultSet rs = helper.executeQuery(sql);
           while (rs.next()) { 
               vtResult.add(rs.getString(columnId));
           }     
                      
           
       }catch(Exception e){
           
       }finally{
           helper.close();
       }
        return vtResult;
    }
    
    
    
    
     public static String deleteRowById(String table,String fieldId,String id){       
       MySQLProviders helper = new MySQLProviders();
       helper.open();
       String sql="DELETE FROM `"+table+"` WHERE `"+fieldId+"`='"+id+"'";    
       try{
            helper.executeUpdate(sql);
       }catch(Exception ex){
          sql=ex.getMessage();
       }finally{
           helper.close();
       }
          
       return sql;
        
     }   
     
     public static String insertUpdate(String table,String sql){       
       MySQLProviders helper = new MySQLProviders();
       helper.open();      
       try{
            helper.executeUpdate(sql);
       }catch(Exception ex){
          sql=ex.getMessage();
       }finally{
           helper.close();
       }
          
       return sql;
        
     }   
    
    
    
}
